package jsyf.oa.dao.report;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by Administrator on 2018/3/7 0007.
 */
@Repository
public class ExhibitionReportDao extends BaseDao {

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return null;
    }

    public JSONObject getExhibitionByCard(String dataTime, String companyId) {
        String years = dataTime.substring(0, 4);
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT \n ");
        sb.append(" COUNT(1) as exhibitionCount,\n ");
        sb.append(" SUM(DATE_FORMAT(EXHIBITION_DATE,'%Y-%m-%d') <= " + "'" + dataTime + "'\n ");
        sb.append(" AND left(EXHIBITION_DATE,4)>=" + "'" + years + "') AS finishExhibition,\n");
        sb.append(" SUM(DATE_FORMAT(EXHIBITION_DATE,'%Y-%m-%d') <= " + "'" + dataTime + "') AS historyExhibition\n");
        sb.append(" from");
        sb.append(" t_crm_exhibition");
        sb.append(" WHERE");
        sb.append(" left(EXHIBITION_DATE,4)=" + "'" + years + "'");
        if (!StringUtils.isBlank(companyId)) {
            sb.append(" AND COMPANY_ID=" + "'" + companyId + "'");
        }
        JSONObject jsonObject = super.queryForJsonObject(sb.toString());
        return jsonObject;
    }


    public List<JSONObject> getExhibitionByCompany(String dateTime) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT  \n ");
        sb.append(" v_sys_company.companyId, \n");
        sb.append(" v_sys_company.companyName, \n ");
        sb.append(" IFNULL(v_sys_company.managerName,'') AS managerName,  \n ");
        sb.append(" IFNULL(v_sys_company.salerManagerName,'') AS salerManagerName, \n ");
        sb.append(" COUNT(1)as unExhibition  \n ");
        sb.append(" FROM  \n ");
        sb.append(" v_sys_company \n");
        sb.append(" LEFT JOIN t_crm_exhibition AS exhibition ON v_sys_company.companyId=exhibition.COMPANY_ID \n");
        sb.append(" AND DATE_FORMAT(exhibition.EXHIBITION_DATE,'%Y-%m-%d')>=" + "'" + dateTime + "'  \n ");
        sb.append(" WHERE 1=1 AND  \n ");
        sb.append(" companyParentId = '201712161613432EAF27DBCCB7D0E6CA' \n ");
        sb.append(" AND companyName NOT LIKE '%大客户%' \n ");
        sb.append(" AND companyDelFlag = 0\n ");
        sb.append(" GROUP BY \n ");
        sb.append(" v_sys_company.companyId\n ");

        List<JSONObject> jsonList = super.queryForJsonList(sb.toString());
        return jsonList;
    }

    public List<JSONObject> getExhibitionList(String condition) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT * FROM V_CRM_EXHIBITION ");
        sbSql.append("WHERE 1=1  " + condition + " ");
        return queryForJsonList(sbSql.toString());
    }

    public List<JSONObject> getRegionListByRegionId(String regionId) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT regionId,parentId,managerName,managerRoleName,managerId,regionName,REPLACE(bigRegionName, '大区', '') as bigRegionName FROM v_sys_region where regionId='" + regionId + "' and parentId=0 ");
        sb.append("AND managerRoleName in('大区经理')  AND  managerJobState<>1 ");
        sb.append("group by managerId  ");
        sb.append("UNION  ");
        sb.append("SELECT regionId,parentId,managerName,managerRoleName,managerId,regionName,REPLACE(bigRegionName, '大区', '') as bigRegionName FROM v_sys_region where parentId='" + regionId + "' and parentId!=0 ");
        sb.append("AND managerRoleName in('区域经理')  AND  managerJobState<>1 ");
        sb.append("group by managerId ");
        return queryForJsonList(sb.toString());
    }

    public List<JSONObject> getExhibitionCustomerInfo(String userId, String exhibitionId) {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT H.regionName bigRegionName,H.userName bigUserName,D.userName,");
        sbSql.append("D.regionName,D.regionId,E.CUSTOM_NUM,E.DEPOSIT_NUM,B.NAME,B.TEL,A.* ");
        sbSql.append("FROM T_CRM_EXHIBITION_CUSTOMER_INFO A ");
        sbSql.append("LEFT JOIN T_WRT_CUSTOMER B ON A.CUSTOMER_ID=B.ID ");
        sbSql.append("LEFT JOIN V_REGION_MANAGER D ON A.CREATE_USER_ID=D.userId ");
        sbSql.append("LEFT JOIN T_CRM_EXHIBITION_AIMS E ON D.regionId=E.REGION_ID  AND E.EXHIBITION_ID='" + exhibitionId + "' ");
        sbSql.append("LEFT JOIN T_SYS_USER G ON A.CREATE_USER_ID=G.ID ");
        sbSql.append("LEFT JOIN V_REGION_MANAGER H ON G.PARENT_ID=H.userId ");
        sbSql.append("WHERE A.CREATE_USER_ID='" + userId + "' ");
        sbSql.append("AND A.EXHIBITION_ID='" + exhibitionId + "' ");
        sbSql.append("order by A.CREATE_USER_ID,TRACK_STATUS ");
        return queryForJsonList(sbSql.toString());
    }
}
